<?php

namespace app\service;

use app\model\EntityModel;
use app\tool\SingletonTrait;
use app\model\ProjectModel;
use support\Request;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;

class EntityService
{
    use SingletonTrait;

    public function __construct()
    {
    }


    /**
     * 获取实体的关联统计
     * @param $projectId
     * @param $param
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getMultipleEntityRelationCount($projectId, $param): array
    {
        $initialEpisodeCodeList = $param['initial_episode_code_list'] ?? [];
        $episodeType = $param['episode_type'] ?? 'episode_make';
        $episodeCodes = $param['episode_codes'] ?? [];

        // 本集使用,获取使用集的sql
        $episodeSql = $this->dealGetMultipleEntityRelationEpisodeSql($initialEpisodeCodeList, $episodeType, $projectId, $episodeCodes);
        $res = [];
        if (empty($param['module_code_list'])) {
            $res = $this->getMultipleEntityRelationCountResult($episodeSql, $param, $param['module_code'] ?? '', $param['module_code'] ?? '');
        }
        if (!empty($param['module_code_list'])) {
            // 查概念数量
            if (in_array('asset', $param['module_code_list'])) {
                $res = $this->getMultipleEntityRelationCountResult($episodeSql, $param, 'design', 'asset');
            }
            // 查分镜数量
            if (in_array('shot', $param['module_code_list'])) {
                $res = $this->getMultipleEntityRelationCountResult($episodeSql, $param, 'storyboard', 'shot');
            }
        }
        return $res;
    }


    /**
     * 获取多实体关联的统计结果
     * @throws ModelNotFoundException
     * @throws DataNotFoundException
     * @throws DbException
     */
    public function getMultipleEntityRelationCountResult($episodeSql, $param, $entityModuleCode, $moduleCodeQuery): array
    {
        $moduleCodeId = 0;
        if (!empty($entityModuleCode)) {
            $moduleCodeId = ModuleService::getInstance()->byCode($entityModuleCode)['id'];
        }
        $db = EntityModel::table('entity');
        if (!empty($episodeSql)) {
            if (!empty($param['episode_type']) && $param['episode_type'] == 'episode_use') {
                $sql = 'select `entity_id` from entity_episode_relation';
                $sql .= " where `project_id` = {$param['project_id']} and `entity_module_id` = {$moduleCodeId} and {$episodeSql} group by entity_id";
                $db->join("($sql) eer", 'eer.entity_id = entity.id');
            } else {
                $db->whereRaw($episodeSql);
            }
        }
        $db = $this->getMultipleEntityRelationCountWhere($param, $db, $entityModuleCode);
        if (in_array($moduleCodeQuery, ['asset', 'level', 'design'])) {
            if ($moduleCodeQuery == 'design' && ($param['type'] ?? '') != 'plan_entity_count') {
                return ['entity_count' => 0, 'category_count' => null];
            }
            return $this->getMultipleEntityRelationAssetReturnData($param, $db, $entityModuleCode, $moduleCodeId);
        }
        if ($moduleCodeQuery == 'shot') {
            return $this->getMultipleEntityRelationShotReturnData($param, $db, $entityModuleCode, $moduleCodeId);
        }
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $defaultEntityCount = $db
            ->field("count(entity.id) as entity_count")
            ->where('project_id', $param['project_id'])
            ->where('module_id', $moduleCodeId)
            ->whereRaw("get_json_string(entity.`json`, '{$moduleCodeQuery}_status') <> $closedId")
            ->find()->toArray();

        $entityCount = null;
        if (!empty($defaultEntityCount['entity_count'])) {
            $entityCount = $defaultEntityCount['entity_count'];
        }
        return ['entity_count' => 0, 'category_count' => $entityCount];
    }

    /**
     * 处理资产返回参数
     * @param $param
     * @param $db
     * @param $entityModuleCode
     * @param $moduleCodeId
     * @return array
     */
    public function getMultipleEntityRelationAssetReturnData($param, $db, $entityModuleCode, $moduleCodeId): array
    {
        $planEntityCountFields = [
            "get_json_string(entity.`json`, '$.{$entityModuleCode}_category') as category",
            "count(entity.`id`) as entity_count",
            "count(get_json_string(entity.`json`, '$.{$entityModuleCode}_grade') = 'S+' or null) as `SS`",
            "count(get_json_string(entity.`json`, '$.{$entityModuleCode}_grade') = 'S' or null) as `S`",
            "count(get_json_string(entity.`json`, '$.{$entityModuleCode}_grade') = 'A' or null) as `A`",
            "count(get_json_string(entity.`json`, '$.{$entityModuleCode}_grade') = 'B' or null) as `B`",
            "count(get_json_string(entity.`json`, '$.{$entityModuleCode}_grade') = 'C' or null) as `C`",
        ];
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $list = $db->where('project_id', $param['project_id'])
            ->where('module_id', $moduleCodeId)
            ->whereRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_status') <> $closedStatusId")
            ->whereRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_grade') !=''")
            ->field($planEntityCountFields)
            ->group("get_json_string(entity.`json`, '$.{$entityModuleCode}_category')")
            ->order('category asc')
            ->select();

        if (empty($list->toArray())) {
            return ['entity_count' => 0, 'category_count' => []];
        }

        $entityCountData = ['entity_count' => 0, 'category_count' => []];
        foreach ($list as $item) {
            $entityCountData['entity_count'] += $item['entity_count'];
            $entityCountData['category_count'][] = [
                'category' => $item['category'],
                'entity_count' => intval($item['entity_count']),
                'asset_grade_list' => [
                    'S+' => intval($item['SS']),
                    'S' => intval($item['S']),
                    'A' => intval($item['A']),
                    'B' => intval($item['B']),
                    'C' => intval($item['C']),
                ]
            ];
        }
        return $entityCountData;
    }

    /**
     * 处理镜头返回参数
     * @param $param
     * @param $db
     * @param $entityModuleCode
     * @param $moduleCodeId
     * @return array
     */
    public function getMultipleEntityRelationShotReturnData($param, $db, $entityModuleCode, $moduleCodeId): array
    {
        $shotCountFields = [
            'count(entity.id) as entity_count',
            "count((get_json_string(entity.`json`, '$.{$entityModuleCode}_perform_form') = '武戏') or null) as martial_arts",
            "count((get_json_string(entity.`json`, '$.{$entityModuleCode}_perform_form') = '文戏') or null) as literary_drama",
            "count((get_json_string(entity.`json`, '$.{$entityModuleCode}_cloth_solution') <> '不需要解算') or null) as solve_shots",
            "count((get_json_string(entity.`json`, '$.{$entityModuleCode}_fx') <> '无特效' and get_json_string(entity.`json`, '$.{$entityModuleCode}_fx') != '' ) or null) as special_effects_shots",
        ];
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $shotCountData = $db->where('project_id', $param['project_id'])
            ->where('module_id', $moduleCodeId)
            ->whereRaw("get_json_string(entity.`json`, '{$entityModuleCode}_status') <> $closedStatusId")
            ->field($shotCountFields)
            ->find();
        if (empty($shotCountData)) {
            return ['entity_count' => 0, 'category_count' => null];
        }
        $returnList['entity_count'] = intval($shotCountData['entity_count']);
        if (!empty($param['type']) && $param['type'] == 'plan_entity_count') {
            $returnList['category_count'] = [
                ['category' => '文戏', 'entity_count' => intval($shotCountData['literary_drama']), 'asset_grade_list' => []],
                ['category' => '武戏', 'entity_count' => intval($shotCountData['martial_arts']), 'asset_grade_list' => []],
                ['category' => '解算', 'entity_count' => intval($shotCountData['solve_shots']), 'asset_grade_list' => []],
                ['category' => '特效', 'entity_count' => intval($shotCountData['special_effects_shots']), 'asset_grade_list' => []],
            ];
            return $returnList;
        }
        $returnList['category_count'] = [
            ['category' => '镜头总数', 'entity_count' => intval($shotCountData['entity_count']), 'asset_grade_list' => []],
            ['category' => '武戏镜头', 'entity_count' => intval($shotCountData['martial_arts']), 'asset_grade_list' => []],
            ['category' => '解算镜头', 'entity_count' => intval($shotCountData['solve_shots']), 'asset_grade_list' => []],
            ['category' => '特效镜头', 'entity_count' => intval($shotCountData['special_effects_shots']), 'asset_grade_list' => []],
        ];
        return $returnList;
    }

    /**
     *获取多实体关联的统计结果 where 条件
     * @param $param
     * @param $db
     * @param $entityModuleCode
     * @return mixed
     */
    public function getMultipleEntityRelationCountWhere($param, $db, $entityModuleCode)
    {
        // 实体查询
        if (!empty($param['entity_ids'])) {
            $db->whereIn('id', $param['entity_ids']);
        }
        // 分类查询
        if (!empty($param['category_name']) && !empty($entityModuleCode)) {
            $categoryName = "'" . implode("','", $param['category_name']) . "'";
            $db->whereRaw("get_json_string(entity.`json`, '{$entityModuleCode}_category') IN ($categoryName)");
        }
        if (!empty($param['entity_name'])) {
            $db->whereRaw("entity.name LIKE '%{$param['entity_name']}%'");
        }
        // 状态查询
        if (!empty($param['status']) && is_array($param['status'])) {
            $statusIds = [];
            foreach ($param['status'] as $statusItem) {
                $statusIds[] = StatusService::getInstance()->byCode($statusItem)['id'];
            }
            $statusIds = join(',', $statusIds);
            $db->whereRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_status') IN ($statusIds)");
        }
        // 资产等级
        if (!empty($param['asset_grade'])) {
            $db->whereFieldRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_grade')", 'IN', $param['asset_grade']);
        }
        // 制作方式
        if (!empty($param['shot_production_method'])) {
            $db->whereFieldRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_production_method')", 'IN', $param['shot_production_method']);
        }
        // 解算方式
        if (!empty($param['shot_cloth_solution'])) {
            $db->whereFieldRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_cloth_solution')", 'IN', $param['shot_cloth_solution']);
        }
        // 特效方式
        if (!empty($param['shot_fx'])) {
            $db->whereFieldRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_fx')", 'IN', $param['shot_fx']);
        }
        // 表演方式
        if (!empty($param['shot_perform_form'])) {
            $db->whereFieldRaw("get_json_string(entity.`json`, '$.{$entityModuleCode}_perform_form')", 'IN', $param['shot_perform_form']);
        }
        // 是否关联
        if (!empty($param['is_relate'])) {
            if ($param['is_relate'] == 'yes') {
                $db->whereRaw("get_json_string(entity.`json`, 'design_relate_level') !=''");
            }
            if ($param['is_relate'] == 'no') {
                $db->whereRaw("((get_json_string(`entity`.`json`, '$.{$entityModuleCode}_relate_level') is null) or (get_json_string(entity.`json`, '$.{$entityModuleCode}_category') = '场景' and get_json_string(entity.`json`, '$.{$entityModuleCode}_relate_level') =''))");
            }
        }
        // 导演是否收藏
        if (!empty($param['director_star']) && $param['director_star'] == 'yes') {
            $db->whereRaw("entity.director_star = 'yes'");
        }
        // 关键镜头
        if (!empty($param['key_shot']) && $param['key_shot'] == 'yes') {
            $db->whereRaw("entity.key_shot = 'yes'");
        }
        // 是否新增
        if (!empty($param['is_append']) && $param['is_append'] == 'yes') {
            $db->whereRaw("entity.is_append = 'yes'");
        }
        if (!empty($param['entity_created']) && count($param['entity_created']) >= 2) {
            $db->whereBetweenTime('entity.created', $param['entity_created'][0], $param['entity_created'][1]);
        }
        return $db;
    }


    /**
     * 使用集与制作集格式查询
     * @param $initialEpisodeCodeList
     * @param $episodeType
     * @param $projectId
     * @param $episodeCodes
     * @return string
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function dealGetMultipleEntityRelationEpisodeSql($initialEpisodeCodeList, $episodeType, $projectId, $episodeCodes): string
    {
        if (empty($initialEpisodeCodeList) && empty($episodeCodes)) { // 剧集,场次筛选
            return '';
        }
        $sql = '';
        if ($episodeType == 'episode_use') { // 本集使用
            if (!empty($initialEpisodeCodeList)) {
                $codes = array_column($initialEpisodeCodeList, 'initial_episode_code');
                $episodeCode = (new \app\model\EntityModel)->where('project_id', $projectId)
                    ->whereIn('code', $codes)
                    ->where('module_id', ModuleService::getInstance()->byCode('episode')['id'])
                    ->field('id,code')
                    ->select(); // 查询集id
                if (empty($episodeCode->toArray())) {
                    return '';
                }
                $episodeCodeMap = array_column($episodeCode->toArray(), null, 'code');
                $initialEpisodeCodeList = array_map(function ($tmp) use ($episodeCodeMap) {
                    $tmp['episode_id'] = $episodeCodeMap[$tmp['initial_episode_code']]['id'] ?? 0;
                    return $tmp;
                }, $initialEpisodeCodeList);
                $sql = EpisodeAllocationService::getInstance()->getEpisodeShowingsSql($initialEpisodeCodeList, 'episode_id', 'showings_number');
            }
            if (!empty($episodeCodes)) {
                $field = [
                    'id as episode_id'
                ];
                $episodeModuleId = ModuleService::getInstance()->byCode('episode', 'id');
                $episodeIds = (new \app\model\EntityModel)->field($field)
                    ->where('project_id', $projectId)
                    ->where('module_id', $episodeModuleId)
                    ->column('episode_id');
                $sql = sprintf("episode_id IN ('%s')", join(',', $episodeIds));
            }
        } else { // 默认初始集-本集制作
            if (!empty($episodeCodes)) {
                $sql = sprintf("initial_episode_code IN ('%s')", join(',', $episodeCodes));
            }
            if (!empty($initialEpisodeCodeList)) {
                $sql = EpisodeAllocationService::getInstance()->getEpisodeShowingsSql($initialEpisodeCodeList, 'initial_episode_code', 'showings_number');
            }
        }
        return $sql;
    }

    /**
     * 获取资产关联的镜头列表
     * @param int $entityId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getAssetRelationShotTaskCardList(int $entityId): array
    {
        $field = [
            'id',
            'module_id',
            'code',
            'project_id',
            'json'
        ];
        $entityData = (new \app\model\EntityModel)->field($field)->where('id', $entityId)->find();
        if (empty($entityData->toArray())) {
            return [];
        }
        $modules = (new \app\model\ModuleModel)->field('*')->whereIn('code', ["design", "asset", "episode", "level", "storyboard"])->select();
        if (empty($modules->toArray())) {
            return [];
        }

        $moduleList = array_column($modules->toArray(), null, 'code');
        $moduleIdMap = array_column($modules->toArray(), null, 'id');

        $designEntityModuleCode = $moduleList['design'];
        $AssetEntityModuleCode = $moduleList['asset'];
        $levelEntityModuleCode = $moduleList['level'];
        $shotModule = $moduleList['storyboard'];

        // 如果是 概念 找到所属的关卡或者资产
        if ($entityData['module_id'] == $designEntityModuleCode['id']) {
            // 判定是资产还是还是关卡
            $designJson = json_decode($entityData['json'], true);
            $currentEntityModuleId = $AssetEntityModuleCode['id'];
            if ($designJson['design_category'] == '关卡') {
                $currentEntityModuleId = $levelEntityModuleCode['id'];
            }
            $currentEntity = (new \app\model\EntityModel)->field('id,name,module_id,project_id')
                ->where('code', $entityData['code'])
                ->where('project_id', $entityData['project_id'])
                ->where('module_id', $currentEntityModuleId)
                ->find();
            if (empty($currentEntity)) {
                return [];
            }
            $entityData = $currentEntity;
        }
        if (empty($moduleIdMap[$entityData['module_id']])) {
            return [];
        }
        $currentModule = $moduleIdMap[$entityData['module_id']];

        $field = [
            'episode.id as id',
            'count(entity.id) as shot_number',
            'entity.initial_episode_code as name',
            "get_json_string(episode.`json`, '$.episode_status') as episode_status",
            "entity.plan_start_time                            as plan_start_time",
            "episode.plan_start_time                           as episode_plan_start_time",
            "episode.plan_end_time                             as episode_plan_end_time",
        ];

        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $episodeTable = "(select id, name, code, project_id, `json`, `plan_start_time`, `plan_end_time` from entity where module_id = {$moduleList['episode']['id']} and project_id = {$entityData['project_id']}) as episode";
        // 查询当前实体 [entityId],被关联的集
        $shotList = (new \app\model\EntityModel)->field($field)
            ->join($episodeTable, '`episode`.`project_id` = entity.project_id and entity.initial_episode_code = episode.code')
            ->where('episode.project_id', $entityData['project_id'])
            ->where('entity.module_id', $shotModule['id'])
            ->whereRaw("find_in_set({$entityData['id']}, get_json_string(entity.`json`, '$.storyboard_{$currentModule['code']}')) > 0")
            ->whereRaw("get_json_string(entity.`json`, 'storyboard_status') <> $closedId")
            ->group("entity.initial_episode_code, entity.showings_number,episode_status,entity.plan_start_time,episode_plan_start_time,episode_plan_end_time,episode.id")
            ->order('entity.initial_episode_code asc,entity.showings_number asc')
            ->select();
        if (empty($shotList->toArray())) {
            return [];
        }
        return array_map(function ($tmp) {
            $tmp['id'] = intval($tmp['id']);
            $tmp['shot_number'] = intval($tmp['shot_number']);
            $tmp['episode_plan_start_time'] = intval($tmp['episode_plan_start_time']);
            $tmp['episode_plan_end_time'] = intval($tmp['episode_plan_end_time']);
            $tmp['episode_status'] = StatusService::getInstance()->byId(intval($tmp['episode_status']));
            return $tmp;
        }, $shotList->toArray());
    }

    /**
     * 获取资产关联的镜头统计
     * @param Request $request
     * @param int $entityId
     * @param int $projectId
     * @param array $page
     * @return void
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getAssetRelationShotCount(Request $request, int $entityId, int $projectId, array $page)
    {
        $res = [];
        // 范围暂时只为资产
        $entity = EntityModel::where('id', $entityId)->find();
        if (empty($entity['id'])) {
            return $res;
        }
        $entityModuleCode = ModuleService::getInstance()->byId($entity['module_id'], 'code');
        if (!in_array($entityModuleCode, ['asset', 'level'])) {
            return $res;
        }
        $dbr = EntityModel::where('project_id', $projectId)
            ->whereRaw('array_contains(split(get_json_string(`entity`.`json`,"$.shot_' . $entityModuleCode . '"), ","), "' . $entityId . '")');
        if (!empty($page) && count($page) > 1) {
            $pageNum = array_shift($page);
            $pageSize = array_shift($page);
            $dbr = $dbr->page($pageNum, $pageSize);
        }
        $res = $dbr->field('initial_episode_code,project_id,count(id) as number')
            ->order('entity.id')
            ->group('initial_episode_code,project_id')
            ->select()->toArray();
        return result_array_construct([
            'initial_episode_code' => 'strval',
            'project_id' => 'intval',
            'number' => 'intval',
        ], $res);
    }

    /**
     * 获取资产关联的镜头列表
     * @param Request $request
     * @param int $entityId
     * @param array $initialEpisodeCode
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getAssetRelationShotList(Request $request, int $entityId, array $initialEpisodeCode): array
    {
        $res = [];
        $entity = EntityModel::where('id', $entityId)->find();
        if (empty($entity)) {
            return $res;
        }
        $moduleService = ModuleService::getInstance();
        $entityModule = $moduleService->byId($entity['module_id']);
        $shotModule = $moduleService->byCode('shot');

        $shotList = EntityModel::where([
            'project_id' => $entity['project_id'],
            'module_id' => $shotModule['id']
        ])
            ->whereFieldRaw('find_in_set("' . $entityId . '", get_json_string(`entity`.`json`, "$.shot_' . $entityModule['code'] . '"))', '>', 0);
        if (!empty($initialEpisodeCode)) {
            $shotList = $shotList
                ->whereIn('initial_episode_code', $initialEpisodeCode);
        }
        $notClosedStatusId = StatusService::getInstance()->byCodeMap(['closed'], true, 'id');
        $shotList = $shotList
            ->field("id, name, code, initial_episode_code, showings_number, remark, json")
            ->whereFieldRaw('get_json_string(`entity`.`json`, "$.shot_status")', 'IN', $notClosedStatusId)
            ->order('initial_episode_code asc, showings_number asc')
            ->select()->toArray();
        //筛选出未封板的剧集
        $episodeList = array_column($shotList, 'initial_episode_code');

        $episodeCode = [];
        $episodeEpisodeIsSeal = [];
        $episodeIsShow = [];
        if (!empty($episodeList)) {
            $episodeModel = $moduleService->byCode('episode');
            $episodeCodeList = EntityModel::where([
                'project_id' => $entity['project_id'],
                'module_id' => $episodeModel['id'],
            ])
                ->whereIn('code', $episodeList)
                ->field([
                    '`code`',
                    'get_json_string(`entity`.`json`, "$.episode_is_seal") as episode_is_seal',
                    'get_json_string(`entity`.`json`, "$.episode_is_show") as episode_is_show'
                ])
//                ->whereFieldRaw('get_json_string(`entity`.`json`, "$.episode_is_seal")', '<>', 'yes') // 去掉限制已播出的集
                ->select()->toArray();
            foreach ($episodeCodeList as $item) {
                $episodeCode[] = $item['code'];
                if (!empty($item['episode_is_seal'])) {
                    $episodeEpisodeIsSeal[] = $item['code'];
                }
                if (!empty($item['episode_is_show'])) {
                    $episodeIsShow[] = $item['code'];
                }
            }
        }

        $epMap = [];
        $statusService = StatusService::getInstance();
        foreach ($shotList as $shotItem) {
            if (!in_array($shotItem['initial_episode_code'], $episodeCode)) {
                continue;
            }
            $shotEntity = [
                'id' => $shotItem['id'],
                'name' => $shotItem['name'],
            ];
            $shotItem['shot_json'] = json_decode($shotItem['json'], true);

            $statusId = intval($shotItem['shot_json']['shot_status']);
            if (!empty($statusId)) {
                $shotEntity['shot_status'] = $statusService->byId($statusId);
            }
            if (!isset($epMap[$shotItem['initial_episode_code']])) {
                $res[] = [
                    "id" => $shotItem['initial_episode_code'],
                    "name" => $shotItem['initial_episode_code'],
                    "episode_is_seal" => in_array($shotItem['initial_episode_code'], $episodeEpisodeIsSeal),
                    "episode_is_show" => in_array($shotItem['initial_episode_code'], $episodeIsShow),
                    'initial_episode_code_id' => $shotItem['initial_episode_code'],
                    'initial_episode_code' => $shotItem['initial_episode_code'],
                    'children' => [
                        [
                            'id' => $shotItem['showings_number'],
                            'name' => $shotItem['showings_number'],
                            'children' => [
                                $shotEntity,
                            ],
                            'shot_number' => 0,
                        ]
                    ],
                    'scene_data_map' => [
                        $shotItem['showings_number'] => 0,
                    ],
                    'shot_number' => 0,
                ];
                $epMap[$shotItem['initial_episode_code']] = count($res) - 1;
            } else {
                $epMapIndex = $epMap[$shotItem['initial_episode_code']];
                if (!isset($res[$epMapIndex]['scene_data_map'][$shotItem['showings_number']])) {
                    $res[$epMapIndex]['scene_data_map'][$shotItem['showings_number']] = count($res[$epMapIndex]['children']);
                    $res[$epMapIndex]['children'][] = [
                        'id' => $shotItem['showings_number'],
                        'name' => $shotItem['showings_number'],
                        'children' => [
                            $shotEntity,
                        ],
                        'shot_number' => 0,
                    ];
                } else {
                    $sceneDataMapIndex = $res[$epMapIndex]['scene_data_map'][$shotItem['showings_number']];
                    $res[$epMapIndex]['children'][$sceneDataMapIndex]['children'][] = $shotEntity;
                }
            }

            $res[$epMap[$shotItem['initial_episode_code']]]['shot_number']++;
            $res[$epMap[$shotItem['initial_episode_code']]]['children'][$res[$epMap[$shotItem['initial_episode_code']]]['scene_data_map'][$shotItem['showings_number']]]['shot_number']++;
        }
        return $res;
    }

    /**
     * 获取多个实体的关联ids
     * @param Request $request
     * @param $entityIds
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getMultipleEntityRelationIds(Request $request, $entityIds)
    {
        $res = [];

        $moduleService = ModuleService::getInstance();

        $entityProjectData = [];
        $project = [];
        $sourceProjectEntityIds = [];

        //1.获取实体对应的项目ID和自身的Code码
        $entityProjectData = EntityModel::whereIn('id', $entityIds)
            ->field('id, project_id, code, module_id, initial_episode_code, showings_number')
            ->select();
        if (empty($entityProjectData->toArray())) {
            return $res;
        }

        //2.获取项目信息
        $entityProjectDataFirst = $entityProjectData[array_key_first($entityProjectData->toArray())];
        $project = ProjectModel::where([
            'id' => $entityProjectDataFirst['project_id']
        ])
            ->find();
        $projectId = $project['id'];
        $entityProjectDataModule = $moduleService->byId($entityProjectDataFirst['module_id']);

        //3.如果是外包项目就拿着甲方项目ID和乙方实体Code找到甲方项目里的关卡ID
        $outsideEntityMap = [];
        if ($project['type'] == 'outsource') {
            $projectId = $project['belong_project_id'];

            $partyADB = [
                '_logic' => 'OR'
            ];

            $insideMap = [];
            if ($entityProjectDataModule['code'] == 'shot') {
                foreach ($entityProjectData as $entityProjectDataItem) {
                    $partyADBItem = [
                        'initial_episode_code' => $entityProjectDataItem['initial_episode_code'],
                        'showings_number' => $entityProjectDataItem['showings_number'],
                        'code' => $entityProjectDataItem['code'],
                    ];
                    $partyADB[] = array_merge(['_logic' => 'AND'], $partyADBItem);

                    $insideMapIndex = implode('_', $partyADBItem);
                    $insideMap[$insideMapIndex] = $entityProjectDataItem['id'];
                }
            } else {
                $entityProjectDataCodes = [];
                foreach ($entityProjectData as $entityProjectDataItem) {
                    $entityProjectDataCodes[] = $entityProjectDataItem['code'];
                    $insideMap[$entityProjectDataItem['code']] = $entityProjectDataItem['id'];
                }
                $partyADB[] = [
                    'code' => ['IN', implode(',', $entityProjectDataCodes)]
                ];
            }

            $entityDataList = EntityModel::where($partyADB)
                ->where([
                    'project_id' => $projectId,
                    'module_id' => $entityProjectDataModule['id'],
                ])
                ->field([
                    'id',
                    'project_id',
                    'code',
                    'module_id',
                    'initial_episode_code',
                    'showings_number',
                ])
                ->select();

            foreach ($entityDataList as $entityDataListItem) {
                $sourceProjectEntityIds[] = $entityDataListItem['id'];
                if ($entityProjectDataModule['code'] == 'shot') {
                    $insideMapIndex = implode('_', [
                        $entityDataListItem['initial_episode_code'],
                        $entityDataListItem['showings_number'],
                        $entityDataListItem['code'],
                    ]);
                    $outsideEntityMap[$entityDataListItem['id']] = $insideMap[$insideMapIndex];
                } else {
                    $outsideEntityMap[$entityDataListItem['id']] = $insideMap[$entityDataListItem['code']];
                }
            }
            $entityIds = $sourceProjectEntityIds;
        }

        switch ($entityProjectDataModule['code']) {
            case 'level':
                //4.通过关卡找出镜头
                $shotModule = $moduleService->byCode('shot');

                //通过镜头查资产
                $shotList = EntityModel::where([
                    'project_id' => $projectId,
                    'module_id' => $shotModule['id']
                ])
                    ->whereFieldRaw('get_json_string(`entity`.`json`, "$.shot_status")', '<>', 3)
                    ->whereFieldRaw('array_length(array_intersect(split(get_json_string(`entity`.`json`,"$.shot_level"), ","), ["' . implode('","', $entityIds) . '"]))', '>', 0)
                    ->field('id')
                    ->fieldRaw('get_json_string(`entity`.`json`, "$.shot_level") as shot_level')
                    ->fieldRaw('ifNull(get_json_string(`entity`.`json`, "$.shot_asset"), "") as shot_asset')
                    ->select();

                $levelShotMap = [];
                $shotAssetMap = [];
                foreach ($shotList as $shotItem) {
                    if (!empty($shotItem['shot_asset'])) {
                        if (!isset($shotAssetMap[$shotItem['id']])) {
                            $shotAssetMap[$shotItem['id']] = [];
                        }

                        $shotAssetMap[$shotItem['id']] = array_merge(
                            $shotAssetMap[$shotItem['id']],
                            explode(',', $shotItem['shot_asset'])
                        );
                    }
                    $currentShotLevel = explode(',', $shotItem['shot_level']);
                    foreach ($currentShotLevel as $currentShotLevelItem) {
                        if (!isset($levelShotMap[$currentShotLevelItem])) {
                            $levelShotMap[$currentShotLevelItem] = [];
                        }
                        if (in_array($currentShotLevelItem, $entityIds)
                            && !in_array($shotItem['id'], $levelShotMap[$currentShotLevelItem])) {

                            $levelShotMap[$currentShotLevelItem][] = $shotItem['id'];
                        }
                    }
                }

                $levelAssetMap = [];
                foreach ($levelShotMap as $levelShotMapIndex => $levelShotMapItem) {
                    $currentShotAssetArray = [];
                    foreach ($levelShotMapItem as $levelShotMapValue) {
                        $currentShotAssetArray = array_merge($currentShotAssetArray, $shotAssetMap[$levelShotMapValue] ?? []);
                    }
                    $levelAssetMap[$levelShotMapIndex] = array_values(array_unique($currentShotAssetArray));
                }
                foreach ($entityIds as $entityId) {
                    if (isset($outsideEntityMap[$entityId])) {
                        $res[$outsideEntityMap[$entityId]] = [
                            'asset_ids' => $levelAssetMap[$entityId] ?? [],
                            'shot_ids' => $levelShotMap[$entityId] ?? [],
                        ];
                    } else {
                        $res[$entityId] = [
                            'asset_ids' => $levelAssetMap[$entityId] ?? [],
                            'shot_ids' => $levelShotMap[$entityId] ?? [],
                        ];
                    }
                }
                break;
            case 'asset':
                $codesModuleMap = $moduleService->byCodeMap(['level', 'shot']);

                // asset to level
                $levelList = EntityModel::where([
                    'project_id' => $projectId,
                    'module_id' => $codesModuleMap['level']['id']
                ])
                    ->whereFieldRaw('get_json_string(`entity`.`json`, "$.level_status")', '<>', 3)
                    ->whereFieldRaw('array_length(array_intersect(split(get_json_string(`entity`.`json`,"$.level_asset"), ","), ["' . implode('","', $entityIds) . '"]))', '>', 0)
                    ->field('id')
                    ->fieldRaw('get_json_string(`entity`.`json`, "$.level_asset") as level_asset')
                    ->select();

                // asset to shot
                $shotList = EntityModel::where([
                    'project_id' => $projectId,
                    'module_id' => $codesModuleMap['shot']['id']
                ])
                    ->whereFieldRaw('get_json_string(`entity`.`json`, "$.shot_status")', '<>', 3)
                    ->whereFieldRaw('array_length(array_intersect(split(get_json_string(`entity`.`json`,"$.shot_asset"), ","), ["' . implode('","', $entityIds) . '"]))', '>', 0)
                    ->field('id')
                    ->fieldRaw('get_json_string(`entity`.`json`, "$.shot_asset") as shot_asset')
                    ->select();

                $assetLevelMap = [];
                foreach ($levelList as $levelItem) {
                    $currentLevelAsset = explode(',', $levelItem['level_asset']);
                    foreach ($currentLevelAsset as $currentLevelAssetItem) {
                        if (!isset($assetLevelMap[$currentLevelAssetItem])) {
                            $assetLevelMap[$currentLevelAssetItem] = [];
                        }
                        if (in_array($currentLevelAssetItem, $entityIds)
                            && !in_array($levelItem['id'], $assetLevelMap[$currentLevelAssetItem])) {

                            $assetLevelMap[$currentLevelAssetItem][] = $levelItem['id'];
                        }
                    }
                }

                $assetShotMap = [];
                foreach ($shotList as $shotItem) {
                    $currentShotAsset = explode(',', $shotItem['shot_asset']);
                    foreach ($currentShotAsset as $currentShotAssetItem) {
                        if (!isset($assetShotMap[$currentShotAssetItem])) {
                            $assetShotMap[$currentShotAssetItem] = [];
                        }
                        if (in_array($currentShotAssetItem, $entityIds)
                            && !in_array($shotItem['id'], $assetShotMap[$currentShotAssetItem])) {

                            $assetShotMap[$currentShotAssetItem][] = $shotItem['id'];
                        }
                    }
                }

                foreach ($entityIds as $entityId) {
                    if (isset($outsideEntityMap[$entityId])) {
                        $res[$outsideEntityMap[$entityId]] = [
                            'level_ids' => $assetLevelMap[$entityId] ?? [],
                            'shot_ids' => $assetShotMap[$entityId] ?? [],
                        ];
                    } else {
                        $res[$entityId] = [
                            'level_ids' => $assetLevelMap[$entityId] ?? [],
                            'shot_ids' => $assetShotMap[$entityId] ?? [],
                        ];
                    }
                }
                break;
            case 'shot':
                $shotList = EntityModel::whereIn('id', $entityIds)
                    ->whereFieldRaw('get_json_string(`entity`.`json`, "$.shot_status")', '<>', 3)
                    ->field('id')
                    ->fieldRaw('get_json_string(`entity`.`json`, "$.shot_asset") as shot_asset')
                    ->fieldRaw('get_json_string(`entity`.`json`, "$.shot_level") as shot_level')
                    ->select();

                foreach ($shotList as $shotItem) {
                    $currentShotAsset = array_filter(explode(',', $shotItem['shot_asset']));
                    $currentShotLevel = array_filter(explode(',', $shotItem['shot_level']));

                    if (isset($outsideEntityMap[$shotItem['id']])) {
                        $res[$outsideEntityMap[$shotItem['id']]] = [
                            'asset_ids' => $currentShotAsset,
                            'level_ids' => $currentShotLevel,
                        ];
                    } else {
                        $res[$shotItem['id']] = [
                            'asset_ids' => $currentShotAsset,
                            'level_ids' => $currentShotLevel,
                        ];
                    }
                }
                break;

        }
        return $res;
    }

    /**
     * 剧集sql 处理
     * @param array $initialEpisodeCodeList
     * @param string $initialEpisodeKey
     * @param string $showingsKey
     * @return string
     */
    public function initialEpisodeCodeSql(array $initialEpisodeCodeList, string $initialEpisodeKey, string $showingsKey): string
    {
        $sqlArr = array_map(function ($initialEpisodeCode) use ($initialEpisodeKey, $showingsKey) {
            $showingsSql = " `" . $initialEpisodeKey . "` = '" . $initialEpisodeCode['initial_episode_code'] . "' ";
            if (!empty($initialEpisodeCode['showings_number'])) {
                $showingsNumberList = array_map('strtoupper', $initialEpisodeCode['showings_number']);
                $showingsSql .= " AND UPPER(" . $showingsKey . ") IN ('" . implode("','", $showingsNumberList) . "') ";
            }
            return $showingsSql;
        }, $initialEpisodeCodeList);

        if (empty($sqlArr)) {
            $sqlArr = ['1=1'];
        }
        return ' (( ' . implode(' ) OR ( ', $sqlArr) . ' ))';
    }
}
